﻿using Npgsql;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;


namespace CrystalizedChlorophyll
{
    public partial class inputKaryawan : Form
    {
        private DataSet ds = new DataSet();
        private DataTable dt = new DataTable();
        ArrayList idCabang = new ArrayList();
        ArrayList cabang = new ArrayList();
        ArrayList idStatus = new ArrayList();
        ArrayList status = new ArrayList();

        public inputKaryawan()
        {
            InitializeComponent();
            dteTglLahir.Format = DateTimePickerFormat.Custom;
            dteTglLahir.CustomFormat = "yyyy-MM-dd";
            dteTglMasuk.Format = DateTimePickerFormat.Custom;
            dteTglMasuk.CustomFormat = "yyyy-MM-dd";
            dteTglKeluar.Format = DateTimePickerFormat.Custom;
            dteTglKeluar.CustomFormat = "yyyy-MM-dd";
            dteTglKeluar.Value = DateTime.Today;

            // PostgeSQL-style connection string
            string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", "localhost", "5432", "Reza Auliandra", "root", "anata_client");
            // Making connection with Npgsql provider
            NpgsqlConnection conn = new NpgsqlConnection(connstring);
            conn.Open();

            // sql statement
            string sql = "SELECT * FROM cabang";
            // making request from our connection
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            // reset DataSet
            ds.Reset();
            // filling DataSet with result from NpgsqlDataAdapter
            da.Fill(ds);
            // C# DataSet can handle multiple tables, select first table
            dt = ds.Tables[0];
            // connect grid to DataTable
            simpleTable.DataSource = dt;
            DataRowCollection dr = dt.Rows;
            DataRow drow;
            for (int i = 0; i < dr.Count; ++i)
            {
                drow = dr[i];
                idCabang.Add(drow[0].ToString());
                cabang.Add(drow[1]);
                cmbCabang.Items.Add(drow[1]);
            }

            // sql statement
            sql = "SELECT * FROM status";
            // making request from our connection
            da = new NpgsqlDataAdapter(sql, conn);
            // reset DataSet
            ds.Reset();
            // filling DataSet with result from NpgsqlDataAdapter
            da.Fill(ds);
            // C# DataSet can handle multiple tables, select first table
            dt = ds.Tables[0];
            // connect grid to DataTable
            simpleTable.DataSource = dt;
            dr = dt.Rows;
            for (int i = 0; i < dr.Count; ++i)
            {
                drow = dr[i];
                idStatus.Add(drow[0].ToString());
                status.Add(drow[1]);
                cmbStatus.Items.Add(drow[1]);
            }

            conn.Close();
        }

        private void addRowButton_Click(object sender, EventArgs e)
        {
            Int32 rowsaffected;

            try
            {
                // PostgeSQL-style connection string
                string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};","localhost", "5432", "Reza Auliandra","root", "anata_client");
                // Making connection with Npgsql provider
                NpgsqlConnection conn = new NpgsqlConnection(connstring);
                conn.Open();

                NpgsqlCommand command = new NpgsqlCommand("INSERT INTO karyawan(nama, alamat, no_telp, no_ktp, is_male, tgl_lahir, id_status, id_agama, id_gol_darah, tgl_masuk, tgl_keluar, id_cabang) VALUES ('" + txtNama.Text + "','" + rtbAlamat.Text +
                    "','" + txtNoTelp.Text + "','" + txtNoKTP.Text + "'," + ((cmbGender.SelectedIndex == 0) ? true : false) + ",'" + dteTglLahir.Text + "','" + (string)idStatus[cmbStatus.SelectedIndex] + "','" + cmbAgama.Text.ToUpper() + "','" + cmbGolDar.Text + "','" + dteTglMasuk.Text + "','" + dteTglKeluar.Text + "','" + (string)idCabang[cmbCabang.SelectedIndex] + "')", conn);
                rowsaffected = command.ExecuteNonQuery();
                Console.WriteLine("It was added {0} lines in simple table", rowsaffected);

                // sql statement
                string sql = "SELECT * FROM karyawan";
                // making request from our connection
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                // reset DataSet
                ds.Reset();
                // filling DataSet with result from NpgsqlDataAdapter
                da.Fill(ds);
                // C# DataSet can handle multiple tables, select first table
                dt = ds.Tables[0];
                // connect grid to DataTable
                simpleTable.DataSource = dt;

                // closing connection
                conn.Close();
            }

            catch (Exception msg)
            {
                // throw error message
                MessageBox.Show(msg.ToString());
                throw;
            }
        }     
    }
}
